package es.udc.emarketapp.emarket.model.product.dao;

import java.util.List;
import java.util.ArrayList;
import java.util.StringTokenizer;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import es.udc.emarketapp.emarket.model.product.vo.ProductVO;
import es.udc.fbellas.j2ee.util.exceptions.InstanceNotFoundException;
import es.udc.fbellas.j2ee.util.exceptions.InternalErrorException;
import es.udc.fbellas.j2ee.util.sql.GeneralOperations;

public abstract class AbstractSQLProductDAO implements SQLProductDAO {

    protected AbstractSQLProductDAO() {
    }

    public boolean exists(Connection connection, Long idProd)
            throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement". 
            String queryString = "SELECT id_prod FROM producto "
                    + "WHERE id_prod = ?";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement". 
            preparedStatement.setLong(1, idProd.longValue());

            // Execute query. 
            resultSet = preparedStatement.executeQuery();

            return resultSet.next();

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

    }//exists

    public ProductVO findById(Connection connection, Long idProd)
            throws InstanceNotFoundException, InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement". 
            String queryString = "SELECT id_vendedor, id_cat, nom_prod, "
                    + "descripcion, pais_origen, uds_disp, imagen, precio, "
                    + "peso, valido FROM producto  WHERE id_prod = ? ";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement". 
            preparedStatement.setLong(1, idProd.longValue());

            // Execute query. 
            resultSet = preparedStatement.executeQuery();

            if (!resultSet.next()) {
                throw new InstanceNotFoundException(idProd,
                        ProductVO.class.getName());
            }

            // Get results. 
            int i = 1;
            String idVendedor = resultSet.getString(i++);
            Long idCat = new Long(resultSet.getLong(i++));
            String nomProd = resultSet.getString(i++);
            String descripcion = resultSet.getString(i++);
            String paisOrigen = resultSet.getString(i++);
            Long udsDisp = new Long(resultSet.getLong(i++));
            byte[] imagen = resultSet.getBytes(i++);
            double precio = resultSet.getDouble(i++);
            double peso = resultSet.getDouble(i++);
            boolean valido = resultSet.getBoolean(i++);

            // Return the value object. 
            return new ProductVO(idProd, idVendedor, idCat, nomProd,
                    descripcion, paisOrigen, udsDisp, imagen, precio, peso, valido);

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

    }//findById

    /*
    public List<ProductVO> findByCategory(Connection connection, Long idCat,
    int startIndex, int count) throws InternalErrorException {

    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {

    // Create "preparedStatement".
    String queryString = "SELECT id_prod, id_vendedor,"
    + "nom_prod, descripcion, pais_origen, uds_disp,"
    + "imagen, precio, peso, valido FROM producto "
    + "WHERE valido = true AND id_cat = ? ";

    preparedStatement = connection.prepareStatement(queryString,
    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

    // Fill "preparedStatement".
    preparedStatement.setLong(1, idCat.longValue());

    // Execute query.
    resultSet = preparedStatement.executeQuery();

    // Read objects.
    List<ProductVO> productoVOs = new ArrayList<ProductVO>();
    int currentCount = 0;


    if ((startIndex >= 1) && resultSet.absolute(startIndex)) {

    do {
    int i = 1;
    Long idProd = new Long(resultSet.getLong(i++));
    String idVendedor = resultSet.getString(i++);
    String nomProd = resultSet.getString(i++);
    String descripcion = resultSet.getString(i++);
    String paisOrigen = resultSet.getString(i++);
    Long udsDisp = new Long(resultSet.getLong(i++));
    byte[] imagen = resultSet.getBytes(i++);
    double precio = resultSet.getDouble(i++);
    double peso = resultSet.getDouble(i++);
    boolean valido = resultSet.getBoolean(i++);

    productoVOs.add(new ProductVO(idProd, idVendedor, idCat,
    nomProd, descripcion, paisOrigen, udsDisp,
    imagen, precio, peso, valido));

    currentCount++;

    } while (resultSet.next() && (currentCount < count));

    }//if

    // Return value objects.
    return productoVOs;

    } catch (SQLException e) {
    throw new InternalErrorException(e);
    } finally {
    GeneralOperations.closeResultSet(resultSet);
    GeneralOperations.closeStatement(preparedStatement);
    }


    }// findByCategory

     */
    public List<ProductVO> findByCategory(Connection connection, Long idCat,
            int levels, int startIndex, int count) throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;


        try {

            // Create "preparedStatement".
            String queryString = "SELECT id_prod, id_vendedor,"
                    + "nom_prod, descripcion, pais_origen, uds_disp,"
                    + "imagen, precio, peso, valido FROM producto "
                    + "WHERE valido = true AND id_cat = ? ";
            //+ "OR id_cat IN (";
            //+ "SELECT id_cat from categoria WHERE cat_padre = ? OR cat_padre IN ("
            //+ "SELECT id_cat from categoria WHERE cat_padre = ?))";


            /*Si hay mas de un nivel buscara en las subcategorias hasta el nivel indicado*/
            if (levels > 0) {

                int level = levels;
                queryString = queryString + "OR id_cat IN (";

                while (level > 1) {
                    queryString = queryString + "SELECT id_cat from categoria WHERE cat_padre = ? OR cat_padre IN (";
                    level--;
                }

                queryString = queryString + "SELECT id_cat from categoria WHERE cat_padre = ?";

                level = levels;
                while (level >= 1) {
                    queryString = queryString + ")";
                    level--;
                }
            }//if(level > 0)

            System.out.println(queryString);

            preparedStatement = connection.prepareStatement(queryString,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            // Fill "preparedStatement".

            int e = 1;
            while (e <= (levels + 1)) {
                preparedStatement.setLong(e, idCat.longValue());
                e++;
            }

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            // Read objects.
            List<ProductVO> productoVOs = new ArrayList<ProductVO>();
            int currentCount = 0;


            if ((startIndex >= 1) && resultSet.absolute(startIndex)) {

                do {
                    int i = 1;
                    Long idProd = new Long(resultSet.getLong(i++));
                    String idVendedor = resultSet.getString(i++);
                    String nomProd = resultSet.getString(i++);
                    String descripcion = resultSet.getString(i++);
                    String paisOrigen = resultSet.getString(i++);
                    Long udsDisp = new Long(resultSet.getLong(i++));
                    byte[] imagen = resultSet.getBytes(i++);
                    double precio = resultSet.getDouble(i++);
                    double peso = resultSet.getDouble(i++);
                    boolean valido = resultSet.getBoolean(i++);

                    productoVOs.add(new ProductVO(idProd, idVendedor, idCat,
                            nomProd, descripcion, paisOrigen, udsDisp,
                            imagen, precio, peso, valido));

                    currentCount++;

                } while (resultSet.next() && (currentCount < count));

            }//if

            // Return value objects.
            return productoVOs;

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }


    }// findByCategory

    /************************/
    public List<ProductVO> findByKeywordsAndCategory(Connection connection,
            String keywords, Long idCat, int levels, int startIndex, int count)
            throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT id_prod, id_vendedor,"
                    + "nom_prod, descripcion, pais_origen, uds_disp,"
                    + "imagen, precio, peso, valido FROM producto "
                    + "WHERE valido = true AND (";

            String palabra;
            int cont = 1;

            StringTokenizer st = new StringTokenizer(keywords);
            while (st.hasMoreTokens()) {
                palabra = st.nextToken();
                if (palabra.length() > 2) {
                    if (cont > 1) {
                        queryString = queryString + " or ";
                    }
                    queryString = queryString + "nom_prod LIKE "
                            + "'%" + palabra + "%'" + " or descripcion LIKE "
                            + "'%" + palabra + "%'";
                    cont++;
                } else {
                    if (cont > 1) {
                        queryString = queryString + " or ";
                    }
                    queryString = queryString + "nom_prod LIKE "
                            + "'" + palabra + "'" + " or descripcion LIKE "
                            + "'" + palabra + "'";
                    cont++;
                }
            }
            queryString = queryString + ") AND (id_cat = ? ";


            /*Si hay mas de un nivel buscara en las subcategorias hasta el nivel indicado*/
            if (levels > 0) {

                int level = levels;
                queryString = queryString + "OR id_cat IN (";

                while (level > 1) {
                    queryString = queryString + "SELECT id_cat from categoria WHERE cat_padre = ? OR cat_padre IN (";
                    level--;
                }

                queryString = queryString + "SELECT id_cat from categoria WHERE cat_padre = ?";

                level = levels + 1;
                while (level >= 1) {
                    queryString = queryString + ")";
                    level--;
                }
            }//if(level > 0)

            System.out.println(queryString);

            preparedStatement = connection.prepareStatement(queryString,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            // Fill "preparedStatement".

            int e = 1;
            while (e <= (levels + 1)) {
                preparedStatement.setLong(e, idCat.longValue());
                e++;
            }

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            // Read objects.
            List<ProductVO> productoVOs = new ArrayList<ProductVO>();
            int currentCount = 0;


            if ((startIndex >= 1) && resultSet.absolute(startIndex)) {

                do {
                    int i = 1;
                    Long idProd = new Long(resultSet.getLong(i++));
                    String idVendedor = resultSet.getString(i++);
                    String nomProd = resultSet.getString(i++);
                    String descripcion = resultSet.getString(i++);
                    String paisOrigen = resultSet.getString(i++);
                    Long udsDisp = new Long(resultSet.getLong(i++));
                    byte[] imagen = resultSet.getBytes(i++);
                    double precio = resultSet.getDouble(i++);
                    double peso = resultSet.getDouble(i++);
                    boolean valido = resultSet.getBoolean(i++);

                    productoVOs.add(new ProductVO(idProd, idVendedor, idCat,
                            nomProd, descripcion, paisOrigen, udsDisp,
                            imagen, precio, peso, valido));

                    currentCount++;

                } while (resultSet.next() && (currentCount < count));

            }//if

            // Return value objects.
            return productoVOs;

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }


    }// findByKeywordsAndCategory

    public List<ProductVO> findByName(Connection connection, String name,
            int startIndex, int count) throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT id_prod, id_vendedor,"
                    + "id_cat, nom_prod, descripcion, pais_origen, uds_disp,"
                    + "imagen, precio, peso, valido FROM producto "
                    + "WHERE nom_prod LIKE ? AND valido = true";

            preparedStatement = connection.prepareStatement(queryString,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            // Fill "preparedStatement".
            preparedStatement.setString(1, "%" + name + "%");

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            // Read objects. 
            List<ProductVO> productoVOs = new ArrayList<ProductVO>();
            int currentCount = 0;


            if ((startIndex >= 1) && resultSet.absolute(startIndex)) {

                do {
                    int i = 1;
                    Long idProd = new Long(resultSet.getLong(i++));
                    String idVendedor = resultSet.getString(i++);
                    Long idCat = new Long(resultSet.getLong(i++));
                    String nomProd = resultSet.getString(i++);
                    String descripcion = resultSet.getString(i++);
                    String paisOrigen = resultSet.getString(i++);
                    Long udsDisp = new Long(resultSet.getLong(i++));
                    byte[] imagen = resultSet.getBytes(i++);
                    double precio = resultSet.getDouble(i++);
                    double peso = resultSet.getDouble(i++);
                    boolean valido = resultSet.getBoolean(i++);

                    productoVOs.add(new ProductVO(idProd, idVendedor, idCat,
                            nomProd, descripcion, paisOrigen, udsDisp,
                            imagen, precio, peso, valido));

                    currentCount++;

                } while (resultSet.next() && (currentCount < count));

            }//if

            // Return value objects.
            return productoVOs;

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }


    }//findByName

    public List<ProductVO> findByKeywords(Connection connection, String keywords,
            int startIndex, int count) throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            /* query para consultar por palabras, solo con tablas MyISAM
            "SELECT nom_prod, descripcion FROM producto WHERE MATCH(nom_prod,
            descripcion) AGAINST (? IN BOOLEAN MODE);
             */

            String queryString = "SELECT * FROM producto WHERE "
                    + "valido = true AND (";
            String palabra;
            int cont = 1;

            StringTokenizer st = new StringTokenizer(keywords);
            while (st.hasMoreTokens()) {
                palabra = st.nextToken();
                if (palabra.length() > 2) {
                    if (cont > 1) {
                        queryString = queryString + " or ";
                    }
                    queryString = queryString + "nom_prod LIKE "
                            + "'%" + palabra + "%'" + " or descripcion LIKE "
                            + "'%" + palabra + "%'";
                    cont++;
                }
            }
            queryString = queryString + ")";

            preparedStatement = connection.prepareStatement(queryString,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            // Read objects.
            List<ProductVO> productoVOs = new ArrayList<ProductVO>();
            int currentCount = 0;


            if ((startIndex >= 1) && resultSet.absolute(startIndex)) {

                do {
                    int i = 1;
                    Long idProd = new Long(resultSet.getLong(i++));
                    String idVendedor = resultSet.getString(i++);
                    Long idCat = new Long(resultSet.getLong(i++));
                    String nomProd = resultSet.getString(i++);
                    String descripcion = resultSet.getString(i++);
                    String paisOrigen = resultSet.getString(i++);
                    Long udsDisp = new Long(resultSet.getLong(i++));
                    byte[] imagen = resultSet.getBytes(i++);
                    double precio = resultSet.getDouble(i++);
                    double peso = resultSet.getDouble(i++);
                    boolean valido = resultSet.getBoolean(i++);

                    productoVOs.add(new ProductVO(idProd, idVendedor, idCat,
                            nomProd, descripcion, paisOrigen, udsDisp,
                            imagen, precio, peso, valido));

                    currentCount++;

                } while (resultSet.next() && (currentCount < count));

            }//if

            // Return value objects.
            return productoVOs;

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }


    }//findByKeywords

    /*
    public List<ProductVO> findByKeywordsAndCategory(Connection connection,
    String keywords, Long idCat, int startIndex, int count)
    throws InternalErrorException {


    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {
    // query para consultar por palabras, solo con tablas MyISAM
    //"SELECT nom_prod, descripcion FROM producto WHERE MATCH(nom_prod,
    //descripcion) AGAINST (? IN BOOLEAN MODE);


    String queryString = "SELECT * FROM producto WHERE "
    + "valido = true AND id_cat = ? AND(";
    String palabra;
    int cont = 1;

    StringTokenizer st = new StringTokenizer(keywords);
    while (st.hasMoreTokens()) {
    palabra = st.nextToken();
    if (palabra.length() > 2) {
    if (cont > 1) {
    queryString = queryString + " or ";
    }
    queryString = queryString + "nom_prod LIKE "
    + "'%" + palabra + "%'" + " or descripcion LIKE "
    + "'%" + palabra + "%'";
    cont++;
    }else{
    if (cont > 1) {
    queryString = queryString + " or ";
    }
    queryString = queryString + "nom_prod LIKE "
    + "'" + palabra + "'" + " or descripcion LIKE "
    + "'" + palabra + "'";
    cont++;
    }
    }
    queryString = queryString + ")";

    preparedStatement = connection.prepareStatement(queryString,
    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);


    preparedStatement.setLong(1, idCat);
    // Execute query.
    resultSet = preparedStatement.executeQuery();

    // Read objects.
    List<ProductVO> productoVOs = new ArrayList<ProductVO>();
    int currentCount = 0;


    if ((startIndex >= 1) && resultSet.absolute(startIndex)) {

    do {
    int i = 1;
    Long idProd = new Long(resultSet.getLong(i++));
    String idVendedor = resultSet.getString(i++);
    i++;
    String nomProd = resultSet.getString(i++);
    String descripcion = resultSet.getString(i++);
    String paisOrigen = resultSet.getString(i++);
    Long udsDisp = new Long(resultSet.getLong(i++));
    byte[] imagen = resultSet.getBytes(i++);
    double precio = resultSet.getDouble(i++);
    double peso = resultSet.getDouble(i++);
    boolean valido = resultSet.getBoolean(i++);

    productoVOs.add(new ProductVO(idProd, idVendedor, idCat,
    nomProd, descripcion, paisOrigen, udsDisp,
    imagen, precio, peso, valido));

    currentCount++;

    } while (resultSet.next() && (currentCount < count));

    }//if

    // Return value objects.
    return productoVOs;

    } catch (SQLException e) {
    throw new InternalErrorException(e);
    } finally {
    GeneralOperations.closeResultSet(resultSet);
    GeneralOperations.closeStatement(preparedStatement);
    }


    }//findByKeywordsAndCategory

     */
    public List<ProductVO> findBySeller(Connection connection,
            String idVendedor, int startIndex, int count)
            throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement". 
            String queryString = "SELECT id_prod, id_cat, nom_prod, "
                    + "descripcion, pais_origen, uds_disp, imagen, precio, "
                    + "peso, valido FROM producto  WHERE id_vendedor = ? "
                    + "AND valido = true";

            preparedStatement = connection.prepareStatement(queryString,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);


            preparedStatement.setString(1, idVendedor);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            // Read objects. 
            List<ProductVO> productoVOs = new ArrayList<ProductVO>();
            int currentCount = 0;

            if ((startIndex >= 1) && resultSet.absolute(startIndex)) {
                do {
                    int i = 1;
                    Long idProd = new Long(resultSet.getLong(i++));
                    Long idCat = new Long(resultSet.getLong(i++));
                    String nomProd = resultSet.getString(i++);
                    String descripcion = resultSet.getString(i++);
                    String paisOrigen = resultSet.getString(i++);
                    Long udsDisp = new Long(resultSet.getLong(i++));
                    byte[] imagen = resultSet.getBytes(i++);
                    double precio = resultSet.getDouble(i++);
                    double peso = resultSet.getDouble(i++);
                    boolean valido = resultSet.getBoolean(i++);

                    productoVOs.add(new ProductVO(idProd, idVendedor, idCat,
                            nomProd, descripcion, paisOrigen, udsDisp,
                            imagen, precio, peso, valido));

                    currentCount++;

                } while (resultSet.next() && (currentCount < count));
            }//if      

            // Return value objects.
            return productoVOs;

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

    }//findBySeller

    public List<ProductVO> findByOrigin(Connection connection,
            String paisOrigen, int startIndex, int count)
            throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT id_prod, id_vendedor, id_cat, "
                    + "nom_prod, descripcion, uds_disp, imagen, precio, "
                    + "peso, valido FROM producto  WHERE pais_origen = ? "
                    + "AND valido = true";

            preparedStatement = connection.prepareStatement(queryString,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);


            preparedStatement.setString(1, paisOrigen);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            // Read objects.
            List<ProductVO> productoVOs = new ArrayList<ProductVO>();
            int currentCount = 0;

            if ((startIndex >= 1) && resultSet.absolute(startIndex)) {
                do {
                    int i = 1;
                    Long idProd = new Long(resultSet.getLong(i++));
                    String idVendedor = resultSet.getString(i++);
                    Long idCat = new Long(resultSet.getLong(i++));
                    String nomProd = resultSet.getString(i++);
                    String descripcion = resultSet.getString(i++);
                    Long udsDisp = new Long(resultSet.getLong(i++));
                    byte[] imagen = resultSet.getBytes(i++);
                    double precio = resultSet.getDouble(i++);
                    double peso = resultSet.getDouble(i++);
                    boolean valido = resultSet.getBoolean(i++);

                    productoVOs.add(new ProductVO(idProd, idVendedor, idCat,
                            nomProd, descripcion, paisOrigen, udsDisp,
                            imagen, precio, peso, valido));

                    currentCount++;

                } while (resultSet.next() && (currentCount < count));
            }//if

            // Return value objects.
            return productoVOs;

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

    }//findByOrigin

    public boolean isValid(Connection connection, Long idProd)
            throws InstanceNotFoundException, InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement". 
            String queryString = "SELECT valido FROM producto "
                    + "WHERE id_prod = ? ";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement". 
            preparedStatement.setLong(1, idProd.longValue());

            // Execute query. 
            resultSet = preparedStatement.executeQuery();

            if (!resultSet.next()) {
                throw new InstanceNotFoundException(idProd.longValue(),
                        ProductVO.class.getName());
            }

            return resultSet.getBoolean(1);

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

    }//isValid

    public void invalidate(Connection connection, Long idProd)
            throws InstanceNotFoundException, InternalErrorException {

        PreparedStatement preparedStatement = null;

        try {

            // Create "preparedStatement". 
            String queryString = "UPDATE producto SET valido = false "
                    + "WHERE id_prod = ?";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement". 
            preparedStatement.setLong(1, idProd.longValue());

            // Execute query. 
            int removedRows = preparedStatement.executeUpdate();

            if (removedRows == 0) {
                throw new InstanceNotFoundException(idProd.longValue(),
                        ProductVO.class.getName());
            }

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeStatement(preparedStatement);
        }

    }//invalidate

    public ProductVO update(Connection connection, ProductVO productVO)
            throws InstanceNotFoundException, InternalErrorException {

        PreparedStatement preparedStatement = null;

        try {

            // Create "preparedStatement".
            String queryString = "UPDATE producto "
                    + "SET id_vendedor = ?, id_cat = ?, nom_prod = ?, "
                    + "descripcion = ?, pais_origen = ?, uds_disp = ?, "
                    + "imagen = ?, precio = ?, peso = ?, valido = ? "
                    + "WHERE id_prod = ? ";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            int i = 1;
            preparedStatement.setString(i++, productVO.getIdVendedor());
            preparedStatement.setLong(i++, productVO.getIdCat().longValue());
            preparedStatement.setString(i++, productVO.getNomProd());
            preparedStatement.setString(i++, productVO.getDescripcion());
            preparedStatement.setString(i++, productVO.getPaisOrigen());
            preparedStatement.setLong(i++, productVO.getUdsDisp().longValue());
            preparedStatement.setBytes(i++, productVO.getImagen());
            preparedStatement.setDouble(i++, productVO.getPrecio());
            preparedStatement.setDouble(i++, productVO.getPeso());
            preparedStatement.setBoolean(i++, productVO.isValido());
            preparedStatement.setLong(i++, productVO.getIdProd().longValue());

            // Execute query. 
            int updatedRows = preparedStatement.executeUpdate();

            if (updatedRows == 0) {
                throw new InstanceNotFoundException(
                        productVO.getIdProd().longValue(),
                        ProductVO.class.getName());
            }

            if (updatedRows > 1) {
                throw new SQLException("Fila duplicada para el id_prod = '"
                        + productVO.getIdProd().longValue()
                        + "' en la tabla 'Producto'");
            }


            return new ProductVO(productVO.getIdProd().longValue(),
                    productVO.getIdVendedor(), productVO.getIdCat().longValue(),
                    productVO.getNomProd(), productVO.getDescripcion(),
                    productVO.getPaisOrigen(), productVO.getUdsDisp().longValue(),
                    productVO.getImagen(),productVO.getPrecio(),
                    productVO.getPeso(), productVO.isValido());

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeStatement(preparedStatement);
        }

    }//update

    public void remove(Connection connection, Long idProd)
            throws InstanceNotFoundException, InternalErrorException {

        PreparedStatement preparedStatement = null;

        try {

            // Create "preparedStatement". 
            String queryString = "DELETE FROM producto WHERE "
                    + "id_prod = ?";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement". 
            preparedStatement.setLong(1, idProd.longValue());

            // Execute query. 
            int removedRows = preparedStatement.executeUpdate();

            if (removedRows == 0) {
                throw new InstanceNotFoundException(idProd.longValue(),
                        ProductVO.class.getName());
            }

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeStatement(preparedStatement);
        }

    }//remove
}
